
package com.zsrt.jmj.student.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zsrt.jmj.student.dao.StudentDao;
import com.zsrt.jmj.student.entity.StudentEntity;
import com.zsrt.jmj.utils.DBUtil;

public class StudentDaoImpl implements StudentDao {

	@Override
	public int add(StudentEntity entity) throws Exception{
		
		int count=0;
		Connection connection = DBUtil.getConnect();
		String sql = "INSERT INTO jmj_student_t(student_name,age,education,teacher_id) VALUES(?,?,?,?)";
		PreparedStatement ps = connection.prepareStatement(sql );

		ps.setString(1, entity.getStudentName());
		ps.setInt(2, entity.getAge());
		ps.setInt(3, entity.getEducation());
		ps.setInt(4, entity.getTeacherId());
		count = ps.executeUpdate();
		DBUtil.closeResources(ps, connection);
		return count;
	}

	@Override
	public List<StudentEntity> findAllStudents() throws SQLException {
		
		List<StudentEntity> list  =new ArrayList<>();
		Connection connection = DBUtil.getConnect();
		String sql = "select stu.ID,stu.student_name,stu.age,ch.children_name,stu.EDUCATION,"
				+ "stu.TEACHER_ID,tea.teacher_name from jmj_student_t stu"
				+ " INNER JOIN jmj_teacher_t tea on tea.ID=stu.TEACHER_ID"
				+ " INNER JOIN jmj_children_t ch on ch.ID = stu.EDUCATION"
				+ " ORDER BY stu.ID DESC";
		PreparedStatement ps = connection.prepareStatement(sql);
		
		ResultSet query = ps.executeQuery();
		while(query.next()){
			StudentEntity entity = new StudentEntity();
			entity.setId(query.getInt("id"));
			entity.setStudentName(query.getString("student_name"));
			entity.setAge(query.getInt("age"));
			entity.setEducation(query.getInt("EDUCATION"));
			entity.setEducationName(query.getString("children_name"));
			
			entity.setTeacherId(query.getInt("TEACHER_ID"));
			entity.setTeacherName(query.getString("teacher_name"));
			
			list.add(entity);
		}
		DBUtil.closeResources(query, ps, connection);
		return list;
	}

	@Override
	public StudentEntity findStudentById(int sId)throws SQLException {
		StudentEntity entity = new StudentEntity();
		
		Connection connection = DBUtil.getConnect();
		String sql = "select * from jmj_student_t where id=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, sId);
		ResultSet query = ps.executeQuery();
		while(query.next()){
			entity.setId(query.getInt(1));
			entity.setStudentName(query.getString(2));
			entity.setAge(query.getInt(3));
			entity.setEducation(query.getInt(4));
			entity.setTeacherId(query.getInt(5));
		}
		return entity;
	}

	@Override
	public int updateById(StudentEntity entity)throws SQLException {
		Connection connection = DBUtil.getConnect();
		String sql = " UPDATE jmj_student_t set STUDENT_NAME=?,AGE=?,EDUCATION=?,TEACHER_ID=? WHERE ID=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setString(1, entity.getStudentName());
		ps.setInt(2, entity.getAge());
		ps.setInt(3, entity.getEducation());
		ps.setInt(4, entity.getTeacherId());
		
		ps.setInt(5, entity.getId());
		int update = ps.executeUpdate();
		return update;
	}

	@Override
	public int deleteById(int cId)throws SQLException {
		
		int counter = 0;
		
		String sql  = "delete from jmj_student_t where ID=?";
		Connection connection = DBUtil.getConnect();
		
		PreparedStatement ps = null;
		ps = connection.prepareStatement(sql);
		ps.setInt(1, cId);
			
		counter = ps.executeUpdate();
		DBUtil.closeResources(ps, connection);
		return counter;
	}

	@Override
	public int deleteByTeacherId(int cId) throws SQLException {
		
		int counter = 0;
		
		String sql  = "delete from jmj_student_t where TEACHER_ID=?";
		Connection connection = DBUtil.getConnect();
		
		PreparedStatement ps = null;
		ps = connection.prepareStatement(sql);
		ps.setInt(1, cId);
			
		counter = ps.executeUpdate();
		DBUtil.closeResources(ps, connection);
		return counter;
	}

}
